if exists (select 1 from sysobjects where name = 'set_zusatzperson' and type = 'P')
begin
   drop procedure set_zusatzperson
   print 'Procedure: set_zusatzperson deleted ...'
end
go
create procedure set_zusatzperson(
  @nachname          varchar(32)   = 'Nachname',
  @vorname           varchar(32)   = 'Vorname',
  @planid            int           = 1,
  @abteilungid       int           = 1
)
as
begin
  set nocount on

  declare @prsid int

   if not exists (select 1 from Person where Nachname = @nachname and Vorname = @vorname and PersonTyp = 2)
   begin
     insert into Person (Vorname, Nachname, Strasse, Strassennr, Postfach, PLZ, Ort, TelnrP, TelnrN, Email, PersonTyp) values (@vorname, @nachname, '', '', '', '', '', '', '', '', 2/*zusatzperson*/)
   end

   select @prsid = PrsId from Person where Nachname = @nachname and Vorname = @vorname and PersonTyp = 2

   if not exists (select 1 from ArbeitsPlanzprs where planID = @planid and zprsID = isnull(@prsid,0) and abteilungID = @abteilungid)
   begin
     insert into ArbeitsPlanzprs(planID, zprsID, abteilungID) values (@planid, @prsid, @abteilungid)
     insert into Arbeitszeit(PrsID,PlanID,AbteilungID,Soll,Haben,Ferien) values (@prsid,@planid,@abteilungid,0,0,0)
   end

end
go
print 'Procedure: set_zusatzperson done ...'
go
grant exec on set_zusatzperson to prsadmins with grant option
go
grant exec on set_zusatzperson to prsusers
go

